package merlion.scm.rmpm.session;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.common.entity.GlobalVariable;
import merlion.common.session.stateless.GlobalVariableSessionBeanLocal;
import merlion.common.util.Consts;
import merlion.scm.rmpm.entity.GoodsReceipt;
import merlion.scm.rmpm.entity.GoodsReceiptRating;
import merlion.scm.rmpm.entity.PurchasePlan;
import merlion.scm.rmpm.entity.RawMaterial;
import merlion.scm.rmpm.entity.RawMaterialItemLine;
import merlion.scm.rmpm.entity.RawMaterialPurchaseOrder;
import merlion.scm.rmpm.entity.RawMaterialSalesOrder;
import merlion.scm.rmpm.entity.RawMaterialSupplyInformation;
import merlion.scm.rmpm.entity.Supplier;
import merlion.scm.rmpm.pojo.RankingRow;
import merlion.scm.rmpm.pojo.SupplierListRow;

@Stateless
public class RawMaterialPurchaseSessionBean implements RawMaterialPurchaseSessionBeanLocal {

    @PersistenceContext
    private EntityManager entityManager;
    @EJB
    private GlobalVariableSessionBeanLocal gvSessionBean;

    @Override
    public List<Supplier> getAllSupplier() {
        return entityManager.createQuery("SELECT s FROM Supplier s").getResultList();
    }

    @Override
    public RawMaterialItemLine calculateLine(RawMaterialItemLine rmil) {

        RawMaterialSupplyInformation rm = rmil.getRawMaterialSupplyInformation();
        rmil.setSubtotalSGD((double) rm.getUnitPriceSGD() * (double) rmil.getQty()*(double)rm.getRawMaterial().getLotSize());
        rmil.setSubtotalUSD((double) rm.getUnitPriceUSD() * (double) rmil.getQty()*(double)rm.getRawMaterial().getLotSize());

        return rmil;
    }

    @Override
    public RawMaterialPurchaseOrder calculatePurchaseOrder(RawMaterialPurchaseOrder rmpo) {

        ArrayList<RawMaterialItemLine> itemList = new ArrayList<RawMaterialItemLine>(rmpo.getItemLines());

        rmpo.setRawMaterialRequestForQuotation(null);
        double subtotalSGD = 0D;
        double subtotalUSD = 0D;
        for (RawMaterialItemLine rmil : itemList) {
            subtotalSGD += rmil.getSubtotalSGD();
            subtotalUSD += rmil.getSubtotalUSD();
        }
        GlobalVariable tax = gvSessionBean.getGlobalVariableByKey(Consts.GLOBAL_TAX_RATE);
        double taxSGD = tax.getDoubleValue().doubleValue() * subtotalSGD;
        double taxUSD = tax.getDoubleValue().doubleValue() * subtotalUSD;
        double totalSGD = subtotalSGD + taxSGD;
        double totalUSD = subtotalUSD + taxUSD;

        rmpo.setSubtotalSGD(subtotalSGD);
        rmpo.setSubtotalUSD(subtotalUSD);
        rmpo.setTaxSGD(taxSGD);
        rmpo.setTaxUSD(taxUSD);
        rmpo.setTotalSGD(totalSGD);
        rmpo.setTotalUSD(totalUSD);

        if (rmpo.getComments() == null || rmpo.getComments().equals("")) {
            rmpo.setComments("No additional comments");
        }

        return rmpo;
    }

    @Override
    public RawMaterialPurchaseOrder savePurchaseOrder(RawMaterialPurchaseOrder rmpo) {
        return entityManager.merge(rmpo);
    }

    @Override
    public List<RawMaterialPurchaseOrder> getAllPurchaseOrder() {
        return entityManager.createQuery("SELECT rmpo FROM RawMaterialPurchaseOrder rmpo").getResultList();
    }

    @Override
    public String generatePurchaseOrderNumber() {
        //Get date YYYYMMDD
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String frontId = sdf.format(cal.getTime());

        //Query SalesOrder table, anything like 'yyMMdd'
        Query query = entityManager.createQuery("SELECT rmpo FROM RawMaterialPurchaseOrder rmpo WHERE rmpo.poNum LIKE :p1");
        query.setParameter("p1", "%" + frontId + "%");

        //Get result size
        int size = query.getResultList().size() + 1;
        String backId = String.format("%04d", size);

        //Set new ID to 'YYYYMMDD' + 'size + 1'
        String id = "RMPO" + frontId + "/" + backId;
        //return id
        return id;
    }

    @Override
    public RawMaterialSalesOrder saveSalesOrder(RawMaterialSalesOrder rmso) {
        rmso.getRmpo().setRmso(rmso);
        return entityManager.merge(rmso);
    }

    @Override
    public List<RawMaterialPurchaseOrder> getAllPurchaseOrderWOSalesOrder() {
        return entityManager.createQuery("SELECT rmpo FROM RawMaterialPurchaseOrder rmpo WHERE rmpo.rmso IS NULL").getResultList();
    }

    @Override
    public List<RawMaterialSalesOrder> getAllSalesOrder() {
        return entityManager.createQuery("SELECT rmso FROM RawMaterialSalesOrder rmso").getResultList();
    }

    @Override
    public List<RawMaterial> getAllRawMaterial() {
        return entityManager.createQuery("SELECT rm FROM RawMaterial rm").getResultList();
    }

    @Override
    public RawMaterial getRawMaterialbyName(String name) {
        return (RawMaterial) entityManager.createQuery("SELECT rm FROM RawMaterial rm WHERE rm.name ='" + name + "'").getResultList().get(0);
    }

    @Override
    public Supplier saveSupplier(Supplier s) {
        ArrayList<RawMaterialSupplyInformation> list = new ArrayList<RawMaterialSupplyInformation>(s.getRawMaterialsSupplied());
        for (RawMaterialSupplyInformation r : list) {
            r.getRawMaterial().getSupplies().add(r);
        }
        return entityManager.merge(s);
    }

    @Override
    public String generateSupplierId() {
        ArrayList<Supplier> suppliers = new ArrayList<Supplier>(entityManager.createQuery("SELECT s FROM Supplier s").getResultList());
        return "SUPP" + String.format("%04d", suppliers.size() + 1);
    }

    @Override
    public Supplier getSupplierById(Long id) {
        return (Supplier) entityManager.createQuery("SELECT s FROM Supplier s WHERE s.id = '" + id + "'").getResultList().get(0);
    }

    @Override
    public List<GoodsReceipt> getUnratedGoodsReceiptBySupplier(Supplier supplier) {
        return entityManager.createQuery("SELECT i FROM GoodsReceipt i WHERE i.supplier.id = '" + supplier.getId() + "' AND i.rating IS NULL").getResultList();
    }

    @Override
    public List<GoodsReceipt> getRatedGoodsReceiptBySupplier(Supplier supplier) {
        return entityManager.createQuery("SELECT i FROM GoodsReceipt i WHERE i.supplier.id = '" + supplier.getId() + "' AND i.rating IS NOT NULL").getResultList();
    }

    @Override
    public GoodsReceiptRating saveGoodsReceiptRating(GoodsReceiptRating rating) {
        return entityManager.merge(rating);
    }

    @Override
    public Double calculateSupplierRating(Supplier supplier) {

        ArrayList<GoodsReceipt> list = new ArrayList<GoodsReceipt>(getRatedGoodsReceiptBySupplier(supplier));

        double totalSum = 0.0D;
        double size = (double) list.size();
        for (GoodsReceipt i : list) {
            totalSum += (double) i.getRating().getOverall();
        }
        double rating = totalSum / size;

        supplier.setRating(rating);
        supplier = entityManager.merge(supplier);

        if (supplier != null) {
            return supplier.getRating();
        } else {
            return null;
        }
    }

    @Override
    public Boolean matchSalesOrderItems(RawMaterialSalesOrder rmso) {

        ArrayList<RawMaterialItemLine> soItems = new ArrayList<RawMaterialItemLine>(rmso.getItemlines());
        ArrayList<RawMaterialItemLine> poItems = new ArrayList<RawMaterialItemLine>(rmso.getRmpo().getItemLines());

        if (soItems.size() != poItems.size()) {
            return false;
        }

        for (int i = 0; i < soItems.size(); i++) {

            RawMaterialItemLine soil = soItems.get(i);
            boolean found = false;
            for (int k = 0; k < poItems.size(); k++) {
                RawMaterialItemLine poil = poItems.get(k);
                if (soil.getRawMaterialSupplyInformation().getRawMaterial().getName().equals(poil.getRawMaterialSupplyInformation().getRawMaterial().getName())
                        && (long) soil.getQty() == (long) poil.getQty()) {
                    found = true;
                }
                if (found) {
                    soItems.remove(i);
                    poItems.remove(k);
                    i = -1;
                    break;
                }
            }
        }

        if (poItems.isEmpty() && soItems.isEmpty()) {
            return true;
        }
        return false;
    }

    @Override
    public Boolean matchSalesOrderTotal(RawMaterialSalesOrder rmso) {
        if (rmso.getCurrency() == rmso.getRmpo().getCurrency()) {
            if (rmso.getCurrency()) {
                if (rmso.getTotal().longValue() == rmso.getRmpo().getTotalSGD().longValue()) {
                    return true;
                }
                return false;
            } else {
                if (rmso.getTotal().longValue() == rmso.getRmpo().getTotalUSD().longValue()) {
                    return true;
                }
                return false;
            }
        }
        return false;
    }

    @Override
    public Boolean matchSalesOrderCurrency(RawMaterialSalesOrder rmso) {
        if (rmso.getCurrency() == rmso.getRmpo().getCurrency()) {
            return true;
        }
        return false;
    }

    @Override
    public ArrayList<RankingRow> rankSupplier(RawMaterial rm, String criteria) {
        ArrayList<RawMaterialSupplyInformation> supplies = new ArrayList<RawMaterialSupplyInformation>(rm.getSupplies());
        ArrayList<RankingRow> rankings = new ArrayList<RankingRow>();

        for (RawMaterialSupplyInformation rmsi : supplies) {
            RankingRow r = new RankingRow();
            r.setSupplier(rmsi.getSupplier());
            r.setOverall(r.getSupplier().getRating());
            r.setPrice(rmsi.getUnitPriceSGD());
            r.setLeadtime(r.getSupplier().getActualLeadTime());
            rankings.add(r);
        }

        if (criteria.equals("Overall Rating")) {
            Collections.sort(rankings, RATING_ORDER);
        } else if (criteria.equals("actual lead time")) {
            Collections.sort(rankings, LEADTIME_ORDER);
        } else if (criteria.equals("Price")) {
            Collections.sort(rankings, PRICE_ORDER);
        }
        return rankings;
    }

    @Override
    public PurchasePlan createPurchasePlan(Collection<SupplierListRow> supplierList, Boolean currency, Boolean urgent) {

        PurchasePlan pp = new PurchasePlan();
        pp.setCurrency(currency);
        pp.setUrgent(urgent);
        pp.setDateCreated(new Timestamp(System.currentTimeMillis()));
        pp.setRmpos(new ArrayList<RawMaterialPurchaseOrder>());

        // Generate Purchase Order Number
        //Get date YYYYMMDD
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String frontId = sdf.format(cal.getTime());
        //Query SalesOrder table, anything like 'yyMMdd'
        Query query = entityManager.createQuery("SELECT rmpo FROM RawMaterialPurchaseOrder rmpo WHERE rmpo.poNum LIKE :p1");
        query.setParameter("p1", "%" + frontId + "%");
        //Get result size
        int size = query.getResultList().size() + 1;

        for (SupplierListRow slr : supplierList) {
            RawMaterialPurchaseOrder rmpo = new RawMaterialPurchaseOrder();
            rmpo.setSupplier(slr.getSupplier());
            rmpo.setAddress(null);
            rmpo.setComments("No additional comments");
            rmpo.setCurrency(currency);
            rmpo.setItemLines(new ArrayList<RawMaterialItemLine>());
            rmpo.setPoDate(new Timestamp(System.currentTimeMillis()));
            rmpo.setPoNum("RMPO" + frontId + "/" + String.format("%04d", size));
            size++;
            rmpo.setRawMaterialRequestForQuotation(null);
            rmpo.setRmso(null);
            rmpo.setUrgent(urgent);

            //Set Item and quantity
            for (int i = 0; i < 23; i++) {
                if ((long) slr.getPurchases().get(i) != 0l) {

                    RawMaterialItemLine rmil = new RawMaterialItemLine();

                    RawMaterial m = slr.getMaterials().get(i);
                    for (RawMaterialSupplyInformation rmsi : slr.getSupplier().getRawMaterialsSupplied()) {
                        if (rmsi.getRawMaterial().equals(m)) {
                            rmil.setRawMaterialSupplyInformation(rmsi);
                        }
                    }

                    rmil.setQty(slr.getPurchases().get(i)/rmil.getRawMaterialSupplyInformation().getRawMaterial().getLotSize());

                    rmil = calculateLine(rmil);
                    rmpo.getItemLines().add(rmil);
                }
            }

            //Calculate
            rmpo = calculatePurchaseOrder(rmpo);
            pp.getRmpos().add(rmpo);
        }

        // Remove PO with 0 itemlines
        ArrayList<RawMaterialPurchaseOrder> toRemove = new ArrayList<RawMaterialPurchaseOrder>();
        for (RawMaterialPurchaseOrder po : pp.getRmpos()) {
            if (po.getItemLines().isEmpty()) {
                toRemove.add(po);
            }
        }
        for (RawMaterialPurchaseOrder po : toRemove) {
            pp.getRmpos().remove(po);
        }

        return pp;
    }

    @Override
    public PurchasePlan savePurchasePlan(PurchasePlan pp) {
        try {
            return entityManager.merge(pp);

        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public Collection<PurchasePlan> getAllPurchasePlan() {
        return entityManager.createQuery("SELECT pp FROM PurchasePlan pp").getResultList();
    }
    static final Comparator<RankingRow> RATING_ORDER = new Comparator<RankingRow>() {

        @Override
        public int compare(RankingRow s1, RankingRow s2) {
            return s2.getOverall().compareTo(s1.getOverall());
        }
    };
    static final Comparator<RankingRow> LEADTIME_ORDER = new Comparator<RankingRow>() {

        @Override
        public int compare(RankingRow s1, RankingRow s2) {
            return s1.getLeadtime().compareTo(s2.getLeadtime());
        }
    };
    static final Comparator<RankingRow> PRICE_ORDER = new Comparator<RankingRow>() {

        @Override
        public int compare(RankingRow i1, RankingRow i2) {
            return i1.getPrice().compareTo(i2.getPrice());
        }
    };

    
}
